package com.yss.reportworld.service.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.*;
import java.util.stream.Collectors;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.alibaba.fastjson.serializer.SerializerFeature;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.yss.common.core.constant.RequestDataHelper;
import com.yss.common.core.domain.TreeNode;
import com.yss.common.core.enums.YssDataBaseEnum;
import com.yss.common.core.enums.YssStatusEnum;
import com.yss.common.core.enums.YssYseNoEnum;
import com.yss.common.core.exception.BusinessException;
import com.yss.common.core.utils.DateUtils;
import com.yss.common.core.utils.JdbcUtil;
import com.yss.common.core.utils.StringUtils;
import com.yss.common.core.utils.uuid.IdUtils;
import com.yss.common.security.utils.DictUtils;
import com.yss.common.security.utils.SecurityUtils;
import com.yss.reportworld.domain.*;
import com.yss.reportworld.enums.ReportWorldDescriptionEnum;
import com.yss.reportworld.enums.ReportWorldEnum;
import com.yss.reportworld.mapper.*;
import com.yss.reportworld.service.*;
import com.yss.reportworld.util.TableInfoUtil;
import com.yss.system.api.domain.SysDictData;
import lombok.extern.slf4j.Slf4j;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.datasource.ConnectionHolder;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.interceptor.TransactionAspectSupport;
import org.springframework.transaction.support.TransactionSynchronizationManager;
import org.springframework.util.CollectionUtils;

import javax.sql.DataSource;

/**
 * 基础数据Service业务层处理
 *
 * @author yss
 * @date 2023-03-29
 */
@Slf4j
@Service("databaseService")
public class DatabaseServiceImpl extends ServiceImpl<DatabaseMapper, Database> implements IDatabaseService {
    @Qualifier("dataSource")
    @Autowired
    private DataSource dataSource;
    @Autowired
    private IDatabaseDisposeService databaseDisposeService;
    @Autowired
    private DataCustomerMapper dataCustomerMapper;
    @Autowired
    private DataModularMapper dataModularMapper;
    @Autowired
    private DataReportMapper dataReportMapper;
    @Autowired
    private DataDemandBugMapper dataDemandBugMapper;
    @Autowired
    private TableMapper tableMapper;
    @Autowired
    private TableDetailMapper tableDetailMapper;
    @Autowired
    private DescriptionMapper descriptionMapper;
    @Autowired
    private IDatabaseCheckService databaseCheckService;
    @Autowired
    private DatabaseDisposeMapper databaseDisposeMapper;
    @Autowired
    private TpGlExhibDatadictMapper tpGlExhibDatadictMapper;

    /**
     * 查询基础数据列表
     *
     * @param database 基础数据
     * @return 基础数据
     */
    @Override
    public List<Database> selectDatabaseList(Database database) {
        try {
            TableInfoUtil.handleTableVersion(database.getHisVersion()); // 替换表名
            QueryWrapper<Database> wrapper = new QueryWrapper();
            wrapper.orderByAsc("PARENT_ID,ORDER_NUM");
            if (StringUtils.isNotEmpty(database.getName())) {
                wrapper.like("NAME", database.getName());
            }
            if (StringUtils.isNotEmpty(database.getCode())) {
                wrapper.like("CODE", database.getCode());
            }
            if (StringUtils.isNotEmpty(database.getVersion())) {
                wrapper.eq("VERSION", database.getVersion());
            }
            if (null != database.getStatus()) {
                wrapper.eq("STATUS", database.getStatus());
            }
            Set<String> permissions = SecurityUtils.getLoginUser().getPermissions();
            if (StringUtils.isEmpty(permissions) || !permissions.contains(ReportWorldEnum.DATABASE_REPORTWORLD_SPECIAL_LIST.getCode())
                    && !SecurityUtils.isAdmin(SecurityUtils.getUserId())) { //管理员
                wrapper.ne("IS_SYSTEM", YssYseNoEnum.YES.getCode());
            }
            List<Database> list = this.list(wrapper);
            if (StringUtils.isEmpty(list)) {
                return list;
            }
            Map<String, String> map = new HashMap<>();
            List<DatabaseDispose> databaseDisposeList = databaseDisposeMapper.selectList(new QueryWrapper<>());
            if (StringUtils.isNotEmpty(databaseDisposeList)) {
                for (DatabaseDispose dispose : databaseDisposeList) {
                    if (StringUtils.isNull(database) || StringUtils.isEmpty(dispose.getTableName())) {
                        continue;
                    }
                    map.put(dispose.getTableName(), dispose.getTableName());
                }
            }
            for (Database base : list) {
                if (StringUtils.isNull(base) || StringUtils.isEmpty(base.getCode())) {
                    continue;
                }
                if (map.containsKey(base.getCode())) {
                    base.setStructure(1);
                    continue;
                }
            }
            return list;
        } finally {
            RequestDataHelper.removeThreadLocal();
        }
    }


    /**
     * 查询基础数据树结构信息
     *
     * @return 基础数据树信息集合
     */
    public List<TreeNode> selectDatabaseTreeList() {
        List<TreeNode> list = new ArrayList<>();
        QueryWrapper<Database> wrapperDatabase = new QueryWrapper<>();
        wrapperDatabase.orderByAsc("PARENT_ID,ORDER_NUM");
        wrapperDatabase.eq("STATUS", YssStatusEnum.NORMAL.getCode());
        List<Database> listDatabase = this.list(wrapperDatabase);
        if (StringUtils.isEmpty(listDatabase)) {
            return list;
        }
        List<String> tempList = listDatabase.stream().map(Database::getId).collect(Collectors.toList());
        Set<String> set = new HashSet<>();
        for (Database database : listDatabase) {
            if (set.contains(database.getParentId()) || tempList.contains(database.getParentId())) {
                continue;
            }
            list.addAll(this.recursionTree(database.getParentId(), listDatabase));
            set.add(database.getParentId());
        }
        return list;
    }

    private List<TreeNode> recursionTree(String id, List<Database> listDatabase) {
        List<TreeNode> list = new ArrayList<>();
        for (Database database : listDatabase) {
            if (!id.equals(database.getParentId())) {
                continue;
            }
            TreeNode treeNode = this.handDatabaseToNode(database);
            List<TreeNode> children = recursionTree(database.getId(), listDatabase);
            treeNode.setChildren(children);
            list.add(treeNode);
        }
        return list;
    }

    /**
     * 将业务功能转化成TreeNode
     */
    private TreeNode handDatabaseToNode(Database database) {
        TreeNode node = new TreeNode();
        node.setId(database.getId());
        node.setParentId(database.getParentId());
        node.setLabel(database.getName());
        return node;
    }

    /**
     * 获取基础信息表格配置
     */
    public List<DatabaseDispose> getDataBaseTableInfo(String queryJson) {
        try {
            Map<String, String> mapType = JSON.parseObject(queryJson, LinkedHashMap.class);
            String code = mapType.get("code");
            String hisVersion = mapType.get("hisVersion");
            TableInfoUtil.handleTableVersion(hisVersion); // 替换表名
            QueryWrapper<DatabaseDispose> wrapper = new QueryWrapper();
            wrapper.eq("TABLE_NAME", code);
            wrapper.orderByAsc("ORDER_NUM");
            List<DatabaseDispose> list = databaseDisposeMapper.selectList(wrapper);
            Map<String, String> mapTypeNew = new LinkedHashMap<>();
            mapTypeNew.put("STRMODULAR", "所属模块");
            mapTypeNew.put("STRREPORT", "所属报表");
            mapTypeNew.put("STRCUSTOMER", "所属客户");
            mapTypeNew.put("RW_DB_VERSION", "版本号");
            mapTypeNew.put("RW_DB_STATUS", "状态");
            mapTypeNew.put("STR_RW_DB_STATUS", "状态");
            mapTypeNew.put("RW_DB_ADD_TIME", "添加时间");
            mapTypeNew.put("RW_DB_ADD_USER", "添加人");
            mapTypeNew.put("RW_DB_EDIT_TIME", "修改时间");
            mapTypeNew.put("RW_DB_EDIT_USER", "修改人");
            mapTypeNew.put("RW_DB_ID", "系统编号");
            for (Map.Entry<String, String> entry : mapTypeNew.entrySet()) {
                DatabaseDispose dispose = new DatabaseDispose();
                dispose.setId(IdUtils.fastSimpleUUID());
                dispose.setIsShow(1);
                if (StringUtils.oneCase(entry.getKey(), "RW_DB_ID,RW_DB_STATUS")) {
                    dispose.setIsShow(2);
                }
                dispose.setWidth(180);
                dispose.setProp(entry.getKey());
                dispose.setLabel(entry.getValue());
                dispose.setComponentType(null);
                list.add(dispose);
            }
            for (DatabaseDispose dispose : list) {
                // 字典配置
                if (StringUtils.isNotEmpty(dispose.getCodeName()) &&
                        StringUtils.oneCase(dispose.getComponentType(), "select,radio")) {
                    if ("report_database_type".equals(dispose.getCodeName())) { //配置的数据库类型
                        Map<String, String> map = DictUtils.getDictCacheMap("report_database_type");
                        List<TpGlExhibDatadict> datadictList = new ArrayList<>();
                        for (Map.Entry<String, String> entry : map.entrySet()) {
                            TpGlExhibDatadict datadict = new TpGlExhibDatadict();
                            datadict.setKeyId(entry.getValue());
                            datadict.setKeyName(entry.getKey());
                            datadictList.add(datadict);
                        }
                        dispose.setOptions(datadictList);
                        continue;
                    }
                    QueryWrapper<TpGlExhibDatadict> wrapperDatadict = new QueryWrapper();
                    wrapperDatadict.eq("CLASS_ID", dispose.getCodeName());
                    dispose.setOptions(tpGlExhibDatadictMapper.selectList(wrapperDatadict));
                }
                if (!mapTypeNew.containsKey(dispose.getProp())) {
                    dispose.setLabel(dispose.getLabel() + ("【" + dispose.getProp() + "】"));
                }
            }
            return list;
        } finally {
            RequestDataHelper.removeThreadLocal();
        }
    }


    /**
     * 通过编码查询基础数据
     *
     * @param database
     * @return
     */
    public Database queryDatabaseOne(Database database) {
        try {
            TableInfoUtil.handleTableVersion(database.getHisVersion()); // 替换表名
            QueryWrapper<Database> wrapperDatabase = new QueryWrapper<>();
            wrapperDatabase.eq("CODE", database.getCode());
            return this.getOne(wrapperDatabase);
        } finally {
            RequestDataHelper.removeThreadLocal();
        }
    }

    /**
     * 查询基础数据
     *
     * @param database
     */
    public Database getDatabaseById(Database database) {
        try {
            TableInfoUtil.handleTableVersion(database.getHisVersion()); // 替换表名
            return this.getById(database.getId());
        } finally {
            RequestDataHelper.removeThreadLocal();
        }
    }

    /**
     * 新增基础数据
     *
     * @param database
     */
    public void addDataBase(Database database) {
        try {
            TableInfoUtil.handleTableVersion(database.getHisVersion()); // 替换表名
            database.setId(IdUtils.fastSimpleUUID());
            database.setCreateBy(SecurityUtils.getUsername());
            database.setCreateTime(new Date());
            this.save(database);
        } finally {
            RequestDataHelper.removeThreadLocal();
        }
    }

    /**
     * 修改基础数据
     *
     * @param database
     */
    public void editDataBase(Database database) {
        try {
            TableInfoUtil.handleTableVersion(database.getHisVersion()); // 替换表名
            database.setUpdateTime(new Date());
            database.setUpdateBy(SecurityUtils.getUsername());
            this.updateById(database);
        } finally {
            RequestDataHelper.removeThreadLocal();
        }
    }

    /**
     * 删除基础数据
     *
     * @param database
     */
    public void delDataBase(Database database) {
        try {
            TableInfoUtil.handleTableVersion(database.getHisVersion()); // 替换表名
            QueryWrapper<Database> wrapper = new QueryWrapper<>();
            wrapper.eq("PARENT_ID", database.getId());
            List<Database> list = this.list(wrapper);
            if (list.size() > 0) {
                throw new BusinessException("存在下级基础数据,不允许删除");
            }
            this.removeById(database.getId());
        } finally {
            RequestDataHelper.removeThreadLocal();
        }
    }


    /**
     * 通过sql查询数据 分页
     *
     * @param queryJson
     * @return
     */
    public Map<String, Object> queryDataBaseByCode(String queryJson) {
        PreparedStatement pStemt = null;
        ResultSet rs = null;
        Connection connection = null;
        try {
            Map<String, String> mapType = JSON.parseObject(queryJson, LinkedHashMap.class);
            String code = mapType.get("code");
            String hisVersion = mapType.get("hisVersion");
            TableInfoUtil.handleTableVersion(hisVersion); // 替换表名
            Map<String, Object> map = new HashMap<>();
            connection = dataSource.getConnection();
            if (null == connection) {
                throw new BusinessException("获取数据库连接信息失败，请稍后重试！");
            }
            QueryWrapper<DatabaseDispose> wrapper = new QueryWrapper();
            wrapper.eq("TABLE_NAME", code);
            wrapper.orderByAsc("ORDER_NUM");
            List<DatabaseDispose> listDispose = databaseDisposeMapper.selectList(wrapper);
            String sql = this.sqlBuffer(queryJson, code, listDispose);
            if (StringUtils.isEmpty(sql)) {
                throw new BusinessException("没有获取到SQL语句！");
            }
            int pageNum = Integer.parseInt(String.valueOf(mapType.get("pageNum")));
            int pageSize = Integer.parseInt(String.valueOf(mapType.get("pageSize")));
            pStemt = connection.prepareStatement(sql + "  limit  " + (pageNum - 1) * pageSize + "," + pageSize);
            rs = pStemt.executeQuery();
            List<Object> list = new ArrayList<>();
            while (rs.next()) {
                Map<String, Object> stringMap = new HashMap<>();
                for (DatabaseDispose dispose : listDispose) {
                    if (StringUtils.isNull(dispose)) {
                        continue;
                    }
                    String colum = dispose.getProp();
                    String columValue = rs.getString(colum);
                    stringMap.put(colum.toUpperCase(), columValue);
                    continue;
                }
                // 显示信息的所属模块，客户，报表信息
                this.getModularCustomerReport(code, "RW_DB_ID", rs.getString("RW_DB_ID"), stringMap);
                stringMap.put("RW_DB_STATUS", rs.getInt("RW_DB_STATUS"));
                stringMap.put("STR_RW_DB_STATUS", YssStatusEnum.getEnumDesc(rs.getInt("RW_DB_STATUS")));
                stringMap.put("RW_DB_VERSION", rs.getString("RW_DB_VERSION"));
                stringMap.put("RW_DB_ADD_TIME", rs.getString("RW_DB_ADD_TIME"));
                stringMap.put("RW_DB_ADD_USER", rs.getString("RW_DB_ADD_USER"));
                stringMap.put("RW_DB_EDIT_TIME", rs.getString("RW_DB_EDIT_TIME"));
                stringMap.put("RW_DB_EDIT_USER", rs.getString("RW_DB_EDIT_USER"));
                stringMap.put("RW_DB_ID", rs.getString("RW_DB_ID"));
                list.add(stringMap);
            }
            map.put("rows", list);
            pStemt = connection.prepareStatement("select count(1) as count from (" + sql + ")  t");
            ResultSet rs1 = pStemt.executeQuery();
            if (rs1.next()) {
                map.put("total", rs1.getBigDecimal("count"));
            }
            return map;
        } catch (Exception ex) {
            throw new BusinessException("查询数据失败！" + ex.getMessage());
        } finally {
            RequestDataHelper.removeThreadLocal();
            try {
                if (rs != null) {   // 关闭记录集
                    rs.close();
                }
                if (pStemt != null) {   // 关闭声明
                    pStemt.close();
                }
                if (connection != null) {   // 关闭声明
                    connection.close();
                }
            } catch (Exception ex) {
                throw new BusinessException("查询数据失败！" + ex.getMessage());
            }
        }
    }


    /**
     * 查询的sql拼接
     *
     * @return
     */
    private String sqlBuffer(String queryJson, String code, List<DatabaseDispose> listDispose) {
        StringBuffer sql = new StringBuffer();
        String dataBaseCode = code.toUpperCase();
        Map<String, String> mapType = JSON.parseObject(queryJson, LinkedHashMap.class);
        String versionSuffix = TableInfoUtil.getVersionSuffix(mapType.get("hisVersion"));
        sql.append("SELECT DISTINCT A.* FROM " + dataBaseCode + versionSuffix + " A");
        Map<String, Object> mapQuery = JSON.parseObject(queryJson, LinkedHashMap.class);
        // 拼接查询条件
        StringBuffer sqlWhere = new StringBuffer();
        // 报表
        if (null != mapQuery.get("strReport") && !"".equals(mapQuery.get("strReport"))) {
            List<String> ids = (List<String>) mapQuery.get("strReport");
            StringJoiner bufferField = this.joinerIds(ids);
            if (null != bufferField) {
                sql.append(" LEFT JOIN REPORT_WORLD_DATA_REPORT" + versionSuffix + " B ON A.RW_DB_ID = B.DATA_ID AND B.DATA_CODE = '" + dataBaseCode + "'");
                sqlWhere.append("   AND B.REPORT_ID IN (" + bufferField + ")");
            }
        }
        // 模块
        if (null != mapQuery.get("strModular") && !"".equals(mapQuery.get("strModular"))) {
            List<String> ids = (List<String>) mapQuery.get("strModular");
            StringJoiner bufferField = this.joinerIds(ids);
            if (null != bufferField) {
                sql.append(" LEFT JOIN REPORT_WORLD_DATA_MODULAR" + versionSuffix + " C ON A.RW_DB_ID = C.DATA_ID AND C.DATA_CODE = '" + dataBaseCode + "'");
                sqlWhere.append("   AND C.MODULAR_ID IN (" + bufferField + ")");
            }
        }
        // 客户
        if (null != mapQuery.get("strCustomer") && !"".equals(mapQuery.get("strCustomer"))) {
            List<String> ids = (List<String>) mapQuery.get("strCustomer");
            StringJoiner bufferField = this.joinerIds(ids);
            if (null != bufferField) {
                sql.append(" LEFT JOIN REPORT_WORLD_DATA_CUSTOMER" + versionSuffix + " D ON A.RW_DB_ID = D.DATA_ID AND D.DATA_CODE = '" + dataBaseCode + "'");
                sqlWhere.append("   AND D.CUSTOMER_ID IN (" + bufferField + ")");
            }
        }
        sql.append(" WHERE 1=1").append(sqlWhere);
        // 状态
        if (null != mapQuery.get("rwDbStatus") && !"".equals(mapQuery.get("rwDbStatus"))) {
            Integer rwDbStatus = (Integer) mapQuery.get("rwDbStatus");
            if (null != rwDbStatus) {
                sql.append("   AND A.RW_DB_STATUS = '" + rwDbStatus + "'");
            }
        }
        // 版本号
        if (null != mapQuery.get("rwDbVersion") && !"".equals(mapQuery.get("rwDbVersion"))) {
            String rwDbVersion = (String) mapQuery.get("rwDbVersion");
            if (StringUtils.isNotBlank(rwDbVersion)) {
                sql.append("   AND A.RW_DB_VERSION = '" + rwDbVersion + "'");
            }
        }
        // 拼接排序字段
        StringJoiner bufferField = new StringJoiner(",");
        bufferField.add(" A.RW_DB_STATUS DESC");
        for (DatabaseDispose dispose : listDispose) {
            if (StringUtils.isNull(dispose) || 2 == dispose.getIsQuery()) {
                continue;
            }
            // 拼接查询条件
            if (null != mapQuery.get(dispose.getProp()) && !"".equals(mapQuery.get(dispose.getProp()))) {
                String prop = (String) mapQuery.get(dispose.getProp());
                if (StringUtils.isEmpty(prop)) {
                    continue;
                }
                if (StringUtils.isNotEmpty(dispose.getSelectType())) {
                    if ("LIKE".equals(dispose.getSelectType())) {
                        sql.append(" AND A." + dispose.getProp() + " " + dispose.getSelectType() + " '%" + prop + "%'");
                    } else {
                        sql.append(" AND A." + dispose.getProp() + " " + dispose.getSelectType() + " '" + prop + "'");
                    }
                }
            }
        }
        if (null != mapQuery.get("orderByColumn") && !"".equals(mapQuery.get("orderByColumn"))) {
            String column = (String) mapQuery.get("orderByColumn");
            String name = " A." + column;
            if (StringUtils.isNotBlank(column)) {
                if ("STRMODULAR".equals(column)) {
                    name = " C.MODULAR_ID ";
                }
                if ("STRCUSTOMER".equals(column)) {
                    name = " D.CUSTOMER_ID ";
                }
                if ("STRREPORT".equals(column)) {
                    name = " B.REPORT_ID ";
                }
                if ("descending".equals(mapQuery.get("isAsc"))) {
                    bufferField.add(name + " DESC");
                } else {
                    bufferField.add(name);
                }
            }
        }
        // 拼接排序 按照业务主键排序
        for (DatabaseDispose dispose : listDispose) {
            if (StringUtils.isNull(dispose) || 2 == dispose.getIsBusinessPrimary()) {
                continue;
            }
            bufferField.add(" A." + dispose.getProp());
        }
        // 导出execl和sql的时候，如果选择了数据，条件带上选择的数据
        String rwDbIdS = mapType.get("ids");
        if (StringUtils.isNotEmpty(rwDbIdS)) {
            List<String> list = JSONArray.parseArray(rwDbIdS, String.class);
            if (StringUtils.isNotEmpty(list)) {
                StringJoiner joiner = this.joinerIds(list);
                sql.append(" AND RW_DB_ID IN(").append(joiner).append(")");
            }
        }
        sql.append(" ORDER BY " + bufferField);
        return sql.toString();
    }

    /**
     * 获取数据对应的模块，客户，报表信息
     *
     * @param colum
     * @param columValue
     * @param stringMap
     */
    private void getModularCustomerReport(String code, String colum, String columValue, Map<String, Object> stringMap) {
        // 用主键字段查询对应的客户，模块，报表信息
        if (!colum.equals("RW_DB_ID")) {
            return;
        }
        // 所属模块
        DataModular dataModular = new DataModular();
        dataModular.setDataId(columValue);
        dataModular.setDataCode(code.toUpperCase());
        List<DataModular> modularList = dataModularMapper.selectPrimaryByDataId(dataModular);
        StringBuffer modularName = new StringBuffer();
        List<String> modularId = new ArrayList<>();
        for (DataModular modular : modularList) {
            modularId.add(modular.getModularId());
            modularName.append(modular.getModularName()).append("/");
        }
        if (StringUtils.isNotBlank(modularName.toString())) {
            stringMap.put("STRMODULAR", modularName.deleteCharAt(modularName.length() - 1).toString());
        }
        stringMap.put("MODULAR", modularId);
        // 所属报表
        DataReport dataReport = new DataReport();
        dataReport.setDataId(columValue);
        dataReport.setDataCode(code.toUpperCase());
        List<DataReport> reportList = dataReportMapper.selectPrimaryByDataId(dataReport);
        StringBuffer reportName = new StringBuffer();
        List<String> reportId = new ArrayList<>();
        for (DataReport report : reportList) {
            reportId.add(report.getReportId());
            reportName.append(report.getReportName()).append("/");
        }
        if (StringUtils.isNotBlank(reportName.toString())) {
            stringMap.put("STRREPORT", reportName.deleteCharAt(reportName.length() - 1).toString());
        }
        stringMap.put("REPORT", reportId);
        // 所属客户
        DataCustomer dataCustomer = new DataCustomer();
        dataCustomer.setDataId(columValue);
        dataCustomer.setDataCode(code.toUpperCase());
        List<DataCustomer> customerList = dataCustomerMapper.selectPrimaryByDataId(dataCustomer);
        StringBuffer customerName = new StringBuffer();
        List<String> customerId = new ArrayList<>();
        for (DataCustomer customer : customerList) {
            customerId.add(customer.getCustomerId());
            customerName.append(customer.getCustomerName()).append("/");
        }
        if (StringUtils.isNotBlank(customerName.toString())) {
            stringMap.put("STRCUSTOMER", customerName.deleteCharAt(customerName.length() - 1).toString());
        }
        stringMap.put("CUSTOMER", customerId);
    }

    /**
     * 拼接查询条件的id
     *
     * @param ids
     * @return
     */
    private StringJoiner joinerIds(List<String> ids) {
        if (StringUtils.isEmpty(ids)) {
            return null;
        }
        if (!CollectionUtils.isEmpty(ids)) {
            StringJoiner bufferField = new StringJoiner(",");
            for (String id : ids) {
                bufferField.add("'" + id + "'");
            }
            return bufferField;
        }
        return null;
    }

    /**
     * 新增基础数据
     *
     * @throws Exception
     */
    @Transactional
    public void addDataBaseInfo(String addJson) {
        PreparedStatement pStemtInsert = null;
        PreparedStatement pStemtSelect = null;
        PreparedStatement pStemtSelectPrimaryKey = null;
        ResultSet rs = null;
        try {
            Map<String, Object> mapType = JSON.parseObject(addJson, LinkedHashMap.class);
            String code = mapType.get("RW_DB_CODE").toString();
            ConnectionHolder holder = (ConnectionHolder) TransactionSynchronizationManager.getResource(this.dataSource);
            Connection connection = holder.getConnection();
            if (null == connection) {
                throw new BusinessException("获取数据库连接出错，请稍后重试！");
            }
            String hisVersion = String.valueOf(mapType.get("hisVersion"));
            TableInfoUtil.handleTableVersion(hisVersion); // 替换表名
            String versionSuffix = TableInfoUtil.getVersionSuffix(hisVersion);
            // 读取数据库的字段类型
            Map<String, String> columnMap = JdbcUtil.getTableColumnNames("SELECT * FROM " + code + versionSuffix, connection);
            StringJoiner bufferFieldInsert = new StringJoiner(",");
            StringJoiner bufferFieldValueInsert = new StringJoiner(",");
            List<Object> listInsert = new ArrayList<>();
            StringBuffer insertSql = new StringBuffer();
            insertSql.append("INSERT INTO " + code + versionSuffix).append("(");
            List<String> dataTypeName = new ArrayList<>(); // 字段对应的类型
            String primaryKeyColumn = "";
            mapType.put("RW_DB_ID", "");
            List<Object> listPk = new ArrayList<>();
            for (Map.Entry<String, Object> info : mapType.entrySet()) {
                if (null == info || StringUtils.isEmpty(info.getKey()) || !columnMap.containsKey(info.getKey())) {
                    continue;
                }
                if (info.getKey().startsWith(ReportWorldEnum.RW_DB_SUFFIX.getCode())
                        && !StringUtils.oneCase(info.getKey(), "RW_DB_STATUS,RW_DB_VERSION,RW_DB_ID")) {
                    continue;
                }
                String type = columnMap.get(info.getKey());
                // 真实主键字段
                if ("RW_DB_ID".equals(info.getKey())) {
                    primaryKeyColumn = IdUtils.fastSimpleUUID();
                    listInsert.add(primaryKeyColumn);
                } else {
                    listInsert.add(info.getValue());
                }
                bufferFieldInsert.add("`" + info.getKey() + "`");
                bufferFieldValueInsert.add("?");
                dataTypeName.add(type);
            }
            // 添加修改时间，修改人
            bufferFieldInsert.add("RW_DB_ADD_TIME,RW_DB_ADD_USER");
            bufferFieldValueInsert.add("?,?");
            listInsert.add(DateUtils.dateToStrByFormat(new Date(), DateUtils.YYYY_MM_DD_HH_MM_SS));
            listInsert.add(SecurityUtils.getUsername());
            dataTypeName.add("DATETIME");
            dataTypeName.add("VARCHAR");
            insertSql.append(bufferFieldInsert).append(") VALUES (").append(bufferFieldValueInsert).append(")");
            pStemtInsert = connection.prepareStatement(insertSql.toString());
            for (int m = 0; m < listInsert.size(); m++) {
                JdbcUtil.addPreparedStatement(m, m, dataTypeName.get(m), listInsert, pStemtInsert);
            }
            pStemtInsert.execute();
            if (StringUtils.isNotBlank(primaryKeyColumn)) {
                String ids = "[\"" + primaryKeyColumn + "\"]";
                this.insertModularCustomerReport(code, ids, mapType);
            }
            String msgInfo = databaseCheckService.checkDataBaseAllByCode(code, connection, versionSuffix);
            if (StringUtils.isNotEmpty(msgInfo)) {
                TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                throw new BusinessException(msgInfo);
            }
            connection.commit();
        } catch (Exception ex) {
            log.error(ex.getMessage());
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            throw new BusinessException("保存基础数据失败！" + ex.getMessage());
        } finally {
            RequestDataHelper.removeThreadLocal();
            try {
                JdbcUtil.close(rs);
                JdbcUtil.close(pStemtInsert);
                JdbcUtil.close(pStemtSelect);
                JdbcUtil.close(pStemtSelectPrimaryKey);
            } catch (Exception ex) {
                log.error(ex.getMessage());
            }
        }
    }

    /* *
     * 修改基础数据
     *
     * @param queryJson
     * @param ids
     * @throws Exception
     */
    @Transactional
    public void updateDataBaseInfo(String updateJson) {
        PreparedStatement pStemt = null;
        PreparedStatement pStemtSelect = null;
        PreparedStatement pStemtSelectPrimaryKey = null;
        ResultSet rs = null;
        try {
            Map<String, Object> mapType = JSON.parseObject(updateJson, LinkedHashMap.class);
            String code = mapType.get("RW_DB_CODE").toString();
            ConnectionHolder holder = (ConnectionHolder) TransactionSynchronizationManager.getResource(this.dataSource);
            Connection connection = holder.getConnection();
            if (null == connection) {
                throw new BusinessException("获取数据库连接出错，请稍后重试！");
            }
            String hisVersion = String.valueOf(mapType.get("hisVersion"));
            TableInfoUtil.handleTableVersion(hisVersion); // 替换表名
            String versionSuffix = TableInfoUtil.getVersionSuffix(hisVersion);
            // 拼接updatey语句
            StringBuffer updateSql = new StringBuffer();
            updateSql.append("UPDATE " + code + versionSuffix + " SET ");
            // 字段和值对应内容
            StringJoiner bufferField = new StringJoiner(",");
            List<Object> updateList = new ArrayList<>();
            List<String> dataTypeName = new ArrayList<>();
            // 拼接要修改的值
            Map<String, String> columnMap = JdbcUtil.getTableColumnNames("SELECT * FROM " + code + versionSuffix, connection);
            for (Map.Entry<String, Object> info : mapType.entrySet()) {
                if (null == info || StringUtils.isEmpty(info.getKey()) || !columnMap.containsKey(info.getKey())) {
                    continue;
                }
                if (info.getKey().startsWith(ReportWorldEnum.RW_DB_SUFFIX.getCode()) && !StringUtils.oneCase(info.getKey(), "RW_DB_STATUS,RW_DB_VERSION")) {
                    continue;
                }
                String type = columnMap.get(info.getKey());
                dataTypeName.add(type);
                bufferField.add("`" + info.getKey() + "`= ?");
                updateList.add(mapType.get(info.getKey()));
            }
            // 添加修改时间，修改人
            bufferField.add("RW_DB_EDIT_TIME =?,RW_DB_EDIT_USER =?");
            updateList.add(DateUtils.dateToStrByFormat(new Date(), DateUtils.YYYY_MM_DD_HH_MM_SS));
            updateList.add(SecurityUtils.getUsername());
            dataTypeName.add("DATETIME");
            dataTypeName.add("VARCHAR");
            updateSql.append(bufferField);
            // 拼接要主键字段
            updateSql.append(" WHERE RW_DB_ID ").append("=?");
            updateList.add(mapType.get("RW_DB_ID"));
            dataTypeName.add("VARCHAR");
            pStemt = connection.prepareStatement(updateSql.toString());
            for (int m = 0; m < updateList.size(); m++) {
                JdbcUtil.addPreparedStatement(m, m, dataTypeName.get(m), updateList, pStemt);
            }
            pStemt.execute();
            // 批量修改
            if (StringUtils.isNotBlank(mapType.get("RW_DB_ID").toString())) {
                String ids = "[\"" + mapType.get("RW_DB_ID") + "\"]";
                this.insertModularCustomerReport(code, ids, mapType);
            }
            String msgInfo = databaseCheckService.checkDataBaseAllByCode(code, connection, versionSuffix);
            if (StringUtils.isNotEmpty(msgInfo)) {
                TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                throw new BusinessException(msgInfo);
            }
            connection.commit();
        } catch (Exception ex) {
            log.error(ex.getMessage());
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            throw new BusinessException("修改数据失败！" + ex.getMessage());
        } finally {
            RequestDataHelper.removeThreadLocal();
            try {
                JdbcUtil.close(pStemt);
                JdbcUtil.close(pStemtSelect);
                JdbcUtil.close(pStemtSelectPrimaryKey);
                JdbcUtil.close(rs);
            } catch (Exception ex) {
                log.error(ex.getMessage());
            }
        }
    }

    /* *
     * 插入数据对应的模块，客户，报表
     *
     * @param code
     * @param ids
     * @param mapType
     * @throws Exception
     */
    public void insertModularCustomerReport(String code, String ids, Map<String, Object> mapType) throws Exception {
        if (StringUtils.isBlank(ids)) {
            log.error("没有获取到选择的数据信息！");
            return;
        }
        // 选中数据的编号
        List<String> list = JSONArray.parseArray(ids, String.class);
        if (StringUtils.isEmpty(list)) {
            log.error("没有获取到选择的数据信息！");
            return;
        }
        List<String> listModular = (List<String>) mapType.get("MODULAR");
        List<String> listReport = (List<String>) mapType.get("REPORT");
        List<String> listCustomer = (List<String>) mapType.get("CUSTOMER");
        // 查询出来的数据处理所属报表，模块，客户的信息
        QueryWrapper<DataModular> modularQueryWrapper = new QueryWrapper<>();
        modularQueryWrapper.in("DATA_ID", list);
        modularQueryWrapper.eq("DATA_CODE", code.toUpperCase());
        dataModularMapper.delete(modularQueryWrapper);
        if (StringUtils.isNotEmpty(listModular)) {
            for (String dataBaseId : list) {
                for (String idModular : listModular) {
                    DataModular modular = new DataModular();
                    modular.setModularId(idModular);
                    modular.setDataId(dataBaseId);
                    modular.setId(IdUtils.fastSimpleUUID());
                    modular.setFileName("手工");
                    modular.setDataCode(code.toUpperCase());
                    // dataModularService.save(modular);
                    dataModularMapper.insert(modular);
                }
            }
        }
        //报表
        QueryWrapper<DataReport> reportQueryWrapper = new QueryWrapper<>();
        reportQueryWrapper.in("DATA_ID", list);
        reportQueryWrapper.eq("DATA_CODE", code.toUpperCase());
        dataReportMapper.delete(reportQueryWrapper);
        if (StringUtils.isNotEmpty(listReport)) {
            for (String dataBaseId : list) {
                for (String idReport : listReport) {
                    DataReport report = new DataReport();
                    report.setReportId(idReport);
                    report.setDataId(dataBaseId);
                    report.setId(IdUtils.fastSimpleUUID());
                    report.setFileName("手工");
                    report.setDataCode(code.toUpperCase());
                    dataReportMapper.insert(report);
                }
            }
        }
        // 客户
        QueryWrapper<DataCustomer> customerQueryWrapper = new QueryWrapper<>();
        customerQueryWrapper.in("DATA_ID", list);
        customerQueryWrapper.eq("DATA_CODE", code.toUpperCase());
        dataCustomerMapper.delete(customerQueryWrapper);
        if (StringUtils.isNotEmpty(listCustomer)) {
            for (String dataBaseId : list) {
                for (String idCustomer : listCustomer) {
                    DataCustomer customer = new DataCustomer();
                    customer.setCustomerId(idCustomer);
                    customer.setDataId(dataBaseId);
                    customer.setId(IdUtils.fastSimpleUUID());
                    customer.setFileName("手工");
                    customer.setDataCode(code.toUpperCase());
                    dataCustomerMapper.insert(customer);
                }
            }
        }
    }

    /**
     * 删除基础数据
     *
     * @param delJson
     * @throws Exception
     */
    @Transactional
    public void deleteDataBaseInfo(String delJson) {
        Map<String, Object> mapType = JSON.parseObject(delJson);
        if (CollectionUtils.isEmpty(mapType)) {
            throw new BusinessException("没有获取到删除的基础数据，请稍后重试！");
        }
        Connection connection = null;
        PreparedStatement pStemt = null;
        try {
            connection = dataSource.getConnection();
            if (null == connection) {
                throw new BusinessException("获取数据库连接出错，请稍后重试！");
            }
            String hisVersion = String.valueOf(mapType.get("hisVersion"));
            TableInfoUtil.handleTableVersion(hisVersion); // 替换表名
            String versionSuffix = TableInfoUtil.getVersionSuffix(hisVersion);
            connection.setAutoCommit(false);
            String code = mapType.get("RW_DB_CODE").toString();
            // 传递过来的主键数据拼接
            StringBuffer buffer = new StringBuffer();
            buffer.append("DELETE FROM " + code + versionSuffix + " WHERE ");
            String ids = mapType.get("ids").toString();
            if (StringUtils.isBlank(ids)) {
                throw new BusinessException("没有获取到删除的基础数据，请稍后重试！");
            }
            List<String> list = JSONArray.parseArray(ids, String.class);
            if (StringUtils.isEmpty(list)) {
                throw new BusinessException("没有获取到删除的基础数据，请稍后重试！");
            }
            StringJoiner bufferColumkey = new StringJoiner(",");
            for (String s : list) {
                bufferColumkey.add("'" + s + "'");
            }
            buffer.append(" RW_DB_ID in(").append(bufferColumkey).append(")");
            // 按照上传得文件名删除
            QueryWrapper<DataModular> modularQueryWrapper = new QueryWrapper<>();
            modularQueryWrapper.eq("DATA_CODE", code);
            modularQueryWrapper.in("DATA_ID", list);
            dataModularMapper.delete(modularQueryWrapper);
            // 报表
            QueryWrapper<DataReport> reportQueryWrapper = new QueryWrapper<>();
            reportQueryWrapper.eq("DATA_CODE", code);
            reportQueryWrapper.in("DATA_ID", list);
            dataReportMapper.delete(reportQueryWrapper);
            // 客户
            QueryWrapper<DataCustomer> customerQueryWrapper = new QueryWrapper<>();
            customerQueryWrapper.eq("DATA_CODE", code);
            customerQueryWrapper.in("DATA_ID", list);
            dataCustomerMapper.delete(customerQueryWrapper);
            // 删除 需求BUG关联关系
            QueryWrapper<DataDemandBug> wrapper = new QueryWrapper<>();
            wrapper.eq("DATA_CODE", code);
            wrapper.in("DATA_ID", list);
            dataDemandBugMapper.delete(wrapper);
            pStemt = connection.prepareStatement(buffer.toString());
            pStemt.executeUpdate();
            String msgInfo = databaseCheckService.checkDataBaseAllByCode(code, connection, versionSuffix);
            if (StringUtils.isNotEmpty(msgInfo)) {
                TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                throw new BusinessException(msgInfo);
            }
            connection.commit();
        } catch (Exception ex) {
            log.error(ex.getMessage());
            try {
                connection.rollback();
            } catch (Exception e) {
                log.error(e.getMessage());
            }
            throw new BusinessException("删除基础数据失败，请稍后重试！" + ex.getMessage());
        } finally {
            RequestDataHelper.removeThreadLocal();
            try {
                JdbcUtil.close(pStemt);
                JdbcUtil.close(connection);
            } catch (Exception ex) {
                log.error(ex.getMessage());
            }
        }
    }

    /**
     * 批量修改状态和版本号
     */
    @Transactional
    public void handleUpdateBatch(String updateJson) {
        PreparedStatement pStemt = null;
        try {
            Map<String, Object> mapType = JSON.parseObject(updateJson, LinkedHashMap.class);
            String code = mapType.get("RW_DB_CODE").toString();
            String ids = mapType.get("ids").toString();
            if (StringUtils.isBlank(ids)) {
                throw new BusinessException("没有获取到修改的基础数据，请稍后重试！");
            }
            List<String> list = JSONArray.parseArray(ids, String.class);
            if (StringUtils.isEmpty(list)) {
                throw new BusinessException("没有获取到修改的基础数据，请稍后重试！");
            }
            ConnectionHolder holder = (ConnectionHolder) TransactionSynchronizationManager.getResource(this.dataSource);
            Connection connection = holder.getConnection();
            if (null == connection) {
                throw new BusinessException("获取数据库连接出错，请稍后重试！");
            }
            String hisVersion = String.valueOf(mapType.get("hisVersion"));
            TableInfoUtil.handleTableVersion(hisVersion); // 替换表名
            String versionSuffix = TableInfoUtil.getVersionSuffix(hisVersion);
            // 版本号和状态
            if (StringUtils.isNotNull(mapType.get("RW_DB_VERSION")) && StringUtils.isNotEmpty(mapType.get("RW_DB_VERSION").toString())) {
                String updateSql = "UPDATE " + code + versionSuffix + " SET RW_DB_VERSION= ?,RW_DB_EDIT_TIME=?,RW_DB_EDIT_USER=? WHERE RW_DB_ID=?";
                pStemt = connection.prepareStatement(updateSql);
                for (String id : list) {
                    if (StringUtils.isBlank(id)) {
                        continue;
                    }
                    pStemt.setString(1, mapType.get("RW_DB_VERSION").toString());
                    pStemt.setTimestamp(2, new java.sql.Timestamp(new Date().getTime()));
                    pStemt.setString(3, SecurityUtils.getUsername());
                    pStemt.setString(4, id);
                    pStemt.addBatch();
                }
                if (pStemt != null) {
                    pStemt.executeBatch();
                }
            }
            if (StringUtils.isNotNull(mapType.get("RW_DB_STATUS")) && null != mapType.get("RW_DB_STATUS")) {
                String updateSql = "UPDATE " + code + versionSuffix + " SET RW_DB_STATUS= ?,RW_DB_EDIT_TIME=?,RW_DB_EDIT_USER=? WHERE RW_DB_ID=?";
                pStemt = connection.prepareStatement(updateSql);
                for (String id : list) {
                    if (StringUtils.isBlank(id)) {
                        continue;
                    }
                    pStemt.setInt(1, Integer.valueOf(mapType.get("RW_DB_STATUS").toString()));
                    pStemt.setTimestamp(2, new java.sql.Timestamp(new Date().getTime()));
                    pStemt.setString(3, SecurityUtils.getUsername());
                    pStemt.setString(4, id);
                    pStemt.addBatch();
                }
                if (pStemt != null) {
                    pStemt.executeBatch();
                }
            }
            List<String> listModular = (List<String>) mapType.get("MODULAR");
            List<String> listReport = (List<String>) mapType.get("REPORT");
            List<String> listCustomer = (List<String>) mapType.get("CUSTOMER");
            boolean clearModular = StringUtils.isNull(mapType.get("clearModular")) ? false : (boolean) mapType.get("clearModular");
            boolean clearCustomer = StringUtils.isNull(mapType.get("clearCustomer")) ? false : (boolean) mapType.get("clearCustomer");
            boolean clearReport = StringUtils.isNull(mapType.get("clearReport")) ? false : (boolean) mapType.get("clearReport");
            // 查询出来的数据处理所属报表，模块，客户的信息
            if (clearModular || StringUtils.isNotEmpty(listModular)) {
                QueryWrapper<DataModular> modularQueryWrapper = new QueryWrapper<>();
                modularQueryWrapper.in("DATA_ID", list);
                modularQueryWrapper.eq("DATA_CODE", code.toUpperCase());
                dataModularMapper.delete(modularQueryWrapper);
            }
            if (clearReport || StringUtils.isNotEmpty(listReport)) {
                QueryWrapper<DataReport> reportQueryWrapper = new QueryWrapper<>();
                reportQueryWrapper.in("DATA_ID", list);
                reportQueryWrapper.eq("DATA_CODE", code.toUpperCase());
                dataReportMapper.delete(reportQueryWrapper);
            }
            if (clearCustomer || StringUtils.isNotEmpty(listCustomer)) {
                QueryWrapper<DataCustomer> customerQueryWrapper = new QueryWrapper<>();
                customerQueryWrapper.in("DATA_ID", list);
                customerQueryWrapper.eq("DATA_CODE", code.toUpperCase());
                dataCustomerMapper.delete(customerQueryWrapper);
            }
            if (StringUtils.isNotEmpty(listModular)) {
                for (String dataBaseId : list) {
                    for (String idModular : listModular) {
                        DataModular modular = new DataModular();
                        modular.setModularId(idModular);
                        modular.setDataId(dataBaseId);
                        modular.setId(IdUtils.fastSimpleUUID());
                        modular.setFileName("批量手工");
                        modular.setDataCode(code.toUpperCase());
                        dataModularMapper.insert(modular);
                    }
                }
            }
            //报表
            if (StringUtils.isNotEmpty(listReport)) {
                for (String dataBaseId : list) {
                    for (String idReport : listReport) {
                        DataReport report = new DataReport();
                        report.setReportId(idReport);
                        report.setDataId(dataBaseId);
                        report.setId(IdUtils.fastSimpleUUID());
                        report.setFileName("批量手工");
                        report.setDataCode(code.toUpperCase());
                        dataReportMapper.insert(report);
                    }
                }
            }
            // 客户
            if (StringUtils.isNotEmpty(listCustomer)) {
                for (String dataBaseId : list) {
                    for (String idCustomer : listCustomer) {
                        DataCustomer customer = new DataCustomer();
                        customer.setCustomerId(idCustomer);
                        customer.setDataId(dataBaseId);
                        customer.setId(IdUtils.fastSimpleUUID());
                        customer.setFileName("批量手工");
                        customer.setDataCode(code.toUpperCase());
                        dataCustomerMapper.insert(customer);
                    }
                }
            }
            String msgInfo = databaseCheckService.checkDataBaseAllByCode(code, connection, versionSuffix);
            if (StringUtils.isNotEmpty(msgInfo)) {
                TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                throw new BusinessException(msgInfo);
            }
        } catch (Exception ex) {
            log.error(ex.getMessage());
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            throw new BusinessException("批量修改数据失败！" + ex.getMessage());
        } finally {
            RequestDataHelper.removeThreadLocal();
            try {
                JdbcUtil.close(pStemt);
            } catch (Exception ex) {
                log.error(ex.getMessage());
            }
        }
    }

    /**
     * 导出JSON的时候查询所有数据
     */
    public Map<String, List<Object>> queryAllDataList(String downversion) {
        PreparedStatement pStemt = null;
        ResultSet rs = null;
        Connection connection = null;
        try {
            // 获取所有的基础数据信息
            QueryWrapper<Database> wrapperDatabase = new QueryWrapper<>();
            wrapperDatabase.eq("STATUS", YssStatusEnum.NORMAL.getCode());
            List<Database> databaseList = this.list(wrapperDatabase);
            if (StringUtils.isEmpty(databaseList)) {
                throw new BusinessException("获取数据库连接信息失败，请稍后重试！");
            }
            connection = dataSource.getConnection();
            if (null == connection) {
                throw new BusinessException("获取数据库连接信息失败，请稍后重试！");
            }
            // 获取修改记录信息文件 取当前版本号的数据
            List<SysDictData> dictDatas = DictUtils.getDictCache(ReportWorldEnum.REPORT_VERSION.getCode());
            if (StringUtils.isEmpty(dictDatas)) {
                throw new BusinessException("没有获取到版本号，请稍后重试！");
            }
            String versionSuffix = StringUtils.getVersionSuffix(downversion);
            Map<String, List<Object>> map = new HashMap<>();
            for (Database database : databaseList) {
                if (null == database || StringUtils.isBlank(database.getCode())) {
                    continue;
                }
                if (null == connection) {
                    throw new BusinessException("获取数据库连接信息失败，请稍后重试！");
                }
                if (StringUtils.oneCase(database.getCode(), ReportWorldEnum.DATABASE_NOT_CODE.getCode())) {
                    continue;
                }
                Map<String, String> columnNames = JdbcUtil.getTableColumnNames("SELECT * FROM " + database.getCode() + versionSuffix, connection);
                if (CollectionUtils.isEmpty(columnNames)) {
                    throw new BusinessException("获取查询结果字段信息失败，请稍后重试！");
                }
                StringBuffer sql = new StringBuffer();
                sql.append("SELECT * FROM " + database.getCode() + versionSuffix + " A WHERE A.RW_DB_STATUS IN (1,2) ");
                QueryWrapper<DatabaseDispose> wrapper = new QueryWrapper();
                wrapper.eq("TABLE_NAME", database.getCode());
                wrapper.eq("IS_BUSINESS_PRIMARY", YssYseNoEnum.YES.getCode());
                List<DatabaseDispose> listDispose = databaseDisposeMapper.selectList(wrapper);
                if (StringUtils.isNotEmpty(listDispose)) {
                    StringJoiner bufferField = new StringJoiner(",");
                    for (DatabaseDispose dispose : listDispose) {
                        if (StringUtils.isNull(dispose)) {
                            continue;
                        }
                        bufferField.add(" A." + dispose.getProp());
                    }
                    sql.append(" ORDER BY " + bufferField);
                }
                pStemt = connection.prepareStatement(sql.toString());
                rs = pStemt.executeQuery();
                List<Object> list = new ArrayList<>();
                //  循环完成在去查询数据对应模块，客户，报表信息
                while (rs.next()) {
                    Map<String, Object> stringMap = new HashMap<>();
                    int a = 1;
                    for (Map.Entry<String, String> info : columnNames.entrySet()) {
                        if (null == info || StringUtils.isBlank(info.getKey())) {
                            a++;
                            continue;
                        }
                        String colum = info.getKey();
                        // 不导出的字段 ,RW_DB_ADD_TIME,RW_DB_ADD_USER,RW_DB_EDIT_TIME,RW_DB_EDIT_USER
                        if (StringUtils.oneCase(colum, "RW_DB_FILE_NAME,RW_DB_DELETE_REASON")) {
                            a++;
                            continue;
                        }
                        if ("DECIMAL".equals(info.getValue())) {
                            stringMap.put(colum, rs.getBigDecimal(a));
                        } else {
                            stringMap.put(colum, rs.getString(a));
                        }
                        a++;
                    }
                    list.add(stringMap);
                }
                map.put(database.getCode(), list);
            }
            return map;
        } catch (Exception ex) {
            throw new BusinessException("查询数据失败！" + ex.getMessage());
        } finally {
            try {
                JdbcUtil.close(rs);
                JdbcUtil.close(pStemt);
                JdbcUtil.close(connection);
            } catch (Exception ex) {
                log.error(ex.getMessage());
            }
        }
    }

    /**
     * 导出EXECL文件,按照配置的显示字段信息导出
     *
     * @param queryJson
     * @return
     * @throws Exception
     */
    public List<Map<String, Object>> dataBaseExecl(String queryJson) {
        PreparedStatement pStemt = null;
        ResultSet rs = null;
        Connection connection = null;
        try {
            Map<String, String> mapType = JSON.parseObject(queryJson, LinkedHashMap.class);
            if (CollectionUtils.isEmpty(mapType)) {
                throw new BusinessException("没有获取到导出的基础数据，请稍后重试！");
            }
            String code = mapType.get("code");
            if (StringUtils.isBlank(code)) {
                throw new BusinessException("没有获取到要导出数据的类型，请稍后重试！");
            }
            String hisVersion = mapType.get("hisVersion");
            TableInfoUtil.handleTableVersion(hisVersion); // 替换表名
            connection = dataSource.getConnection();
            if (null == connection) {
                throw new BusinessException("获取数据库连接信息失败，请稍后重试！");
            }
            QueryWrapper<DatabaseDispose> wrapper = new QueryWrapper();
            wrapper.eq("TABLE_NAME", code);
            wrapper.orderByAsc("ORDER_NUM");
            List<DatabaseDispose> listDispose = databaseDisposeMapper.selectList(wrapper);
            String sql = this.sqlBuffer(queryJson, code, listDispose);
            if (StringUtils.isBlank(sql)) {
                throw new BusinessException("没有获取到SQL语句！");
            }
            pStemt = connection.prepareStatement(sql);
            rs = pStemt.executeQuery();
            List<Map<String, Object>> list = new ArrayList();
            while (rs.next()) {
                Map<String, Object> row = new LinkedHashMap<>();
                for (DatabaseDispose dispose : listDispose) {
                    if (StringUtils.isNull(dispose) || StringUtils.isEmpty(dispose.getProp())
                            || 2 == dispose.getIsShow()) {
                        continue;
                    }
                    row.put(dispose.getLabel() + ("【" + dispose.getProp() + "】"), rs.getString(dispose.getProp()));
                }
                list.add(row);
            }
            return list;
        } catch (Exception ex) {
            log.error(ex.getMessage());
            throw new BusinessException("生成EXECL文件失败！" + ex.getMessage());
        } finally {
            RequestDataHelper.removeThreadLocal();
            try {
                JdbcUtil.close(rs);
                JdbcUtil.close(pStemt);
                JdbcUtil.close(connection);
            } catch (Exception ex) {
                log.error(ex.getMessage());
            }

        }
    }


    /**
     * 导出SQL语句
     *
     * @param queryJson
     * @return
     * @throws Exception
     */
    public String dataBaseSql(String queryJson) {
        PreparedStatement pStemt = null;
        ResultSet rs = null;
        Connection connection = null;
        StringBuffer buffer = new StringBuffer();
        try {
            Map<String, String> mapType = JSON.parseObject(queryJson, LinkedHashMap.class);
            String code = mapType.get("code");
            if (StringUtils.isBlank(code)) {
                throw new BusinessException("没有获取到要导出数据的类型，请稍后重试！");
            }

            if (StringUtils.isBlank(code)) {
                throw new BusinessException("没有获取到对应的基础数据配置信息，请稍后重试！");
            }
            if (CollectionUtils.isEmpty(mapType)) {
                throw new BusinessException(code + "没有获取到维护展示字段信息！");
            }
            connection = dataSource.getConnection();
            if (null == connection) {
                throw new BusinessException("获取数据库连接信息失败，请稍后重试！");
            }
            String hisVersion = mapType.get("hisVersion");
            String versionSuffix = TableInfoUtil.getVersionSuffix(hisVersion);
            TableInfoUtil.handleTableVersion(hisVersion); // 替换表名
            Map<String, String> columnNames = JdbcUtil.getTableColumnNames("SELECT * FROM " + code + versionSuffix, connection);
            if (CollectionUtils.isEmpty(columnNames)) {
                throw new BusinessException("获取查询结果字段信息失败，请稍后重试！");
            }
            QueryWrapper<DatabaseDispose> wrapper = new QueryWrapper();
            wrapper.eq("TABLE_NAME", code);
            wrapper.eq("STATUS", YssStatusEnum.NORMAL.getCode());
            wrapper.orderByAsc("ORDER_NUM");
            List<DatabaseDispose> listDispose = databaseDisposeMapper.selectList(wrapper);
            String sql = this.sqlBuffer(queryJson, code, listDispose);
            if (StringUtils.isBlank(sql)) {
                throw new BusinessException("没有获取到SQL语句！");
            }
            String sqlType = YssDataBaseEnum.ORACLE.getCode();
            if (StringUtils.isNotBlank(queryJson)) {
                Map<String, String> mapQuery = JSON.parseObject(queryJson, LinkedHashMap.class);
                if (StringUtils.isNotEmpty(mapQuery.get("DATABASETYPE"))) {
                    sqlType = mapQuery.get("DATABASETYPE");
                }
            }
            pStemt = connection.prepareStatement(sql);
            rs = pStemt.executeQuery();
            while (rs.next()) {
                StringBuffer insertSql = new StringBuffer();
                insertSql.append("INSERT INTO " + code.replace(ReportWorldEnum.RW_DB_SUFFIX.getCode(), "")).append("(");
                StringJoiner bufferFieldInsert = new StringJoiner(",");
                StringJoiner bufferFieldValueInsert = new StringJoiner(",");
                for (DatabaseDispose dispose : listDispose) {
                    if (StringUtils.isNull(dispose)) {
                        continue;
                    }
                    String value = rs.getString(dispose.getProp());
                    if (StringUtils.isBlank(value)) {
                        bufferFieldInsert.add(dispose.getProp());
                        bufferFieldValueInsert.add(null);
                        continue;
                    }
                    if (value.indexOf("'") > -1) {
                        value = value.replaceAll("\\'", "\\'\\'");
                    }
                    switch (columnNames.get(dispose.getProp())) {
                        case "VARCHAR":
                            value = "'" + value + "'";
                            break;
                        case "DECIMAL":
                            break;
                        case "DATETIME":
                            value = "TIMESTAMP '" + value + "'";
                            break;
                        case "DATE":
                            value = "TO_DATE(" + value + ",'" + DateUtils.YYYY_MM_DD + "')";
                            if (YssDataBaseEnum.MYSQL.getCode().equals(sqlType)) {
                                value = "DATE_FORMAT(" + value + ",'%Y-%m-%d')";
                            }
                            break;
                        default:
                            throw new BusinessException("没有获取到对应的数据库字段类型，请联系管理员！");
                    }
                    if (YssDataBaseEnum.MYSQL.getCode().equals(sqlType)) {
                        bufferFieldInsert.add("`" + dispose.getProp() + "`");
                    } else {
                        bufferFieldInsert.add(dispose.getProp());
                    }
                    bufferFieldValueInsert.add(value);
                }
                insertSql.append(bufferFieldInsert).append(") VALUES (").append(bufferFieldValueInsert).append(");");
                buffer.append(insertSql).append("\r\n");
            }
        } catch (Exception ex) {
            try {
                connection.rollback();
            } catch (Exception e) {
                log.error(e.getMessage());
            }
            throw new BusinessException("生成插入语句失败！" + ex.getMessage());
        } finally {
            RequestDataHelper.removeThreadLocal();
            try {
                JdbcUtil.close(rs);
                JdbcUtil.close(pStemt);
                JdbcUtil.close(connection);
            } catch (Exception ex) {
                log.error(ex.getMessage());
            }
        }
        return buffer.toString();
    }

    /**
     * 构建基础数据信息，可以生成表和需要得配置信息，包括备份和升级策略信息
     */
    @Transactional
    public void structureDataBase(Database database) {
        PreparedStatement pStemt = null;
        try {
            String code = database.getCode();
            if (StringUtils.isEmpty(code)) {
                throw new BusinessException("没有获取到你要构建的基础数据的编码！");
            }
            QueryWrapper<Database> databaseQueryWrapper = new QueryWrapper<>();
            databaseQueryWrapper.eq("CODE", code);
            databaseQueryWrapper.eq("STATUS", YssStatusEnum.NORMAL.getCode());
            Database databaseInfo = this.getOne(databaseQueryWrapper);
            if (StringUtils.isNull(databaseInfo)) {
                throw new BusinessException("没有获取到配置信息");
            }
            // 生成建表语句
            QueryWrapper<Table> tableQueryWrapper = new QueryWrapper<>();
            tableQueryWrapper.eq("NAME", code.replace(ReportWorldEnum.RW_DB_SUFFIX.getCode(), ""));
            tableQueryWrapper.eq("STATUS", YssStatusEnum.NORMAL.getCode());
            Table table = tableMapper.selectOne(tableQueryWrapper);
            if (StringUtils.isNull(table)) {
                throw new BusinessException("没有获取到表信息");
            }
            QueryWrapper<TableDetail> detailQueryWrapper = new QueryWrapper<>();
            detailQueryWrapper.eq("TABLE_ID", table.getId());
            detailQueryWrapper.eq("STATUS", YssStatusEnum.NORMAL.getCode());
            List<TableDetail> detailList = tableDetailMapper.selectList(detailQueryWrapper);
            if (StringUtils.isEmpty(detailList)) {
                throw new BusinessException("没有获取到表字段信息");
            }
            for (TableDetail tableDetail : detailList) {
                if (1 == tableDetail.getPrimaryKey()) { //清除原来主键
                    tableDetail.setPrimaryKey(2);
                    continue;
                }
            }
            Map<String, String> mapTypeNew = new LinkedHashMap<>();
            if (1 == databaseInfo.getDataBaseType()) {
                mapTypeNew.put("RW_DB_DATABASE_TYPE", "数据库类型,1,20");
            }
            // 添加固定字段
            mapTypeNew.put("RW_DB_FILE_NAME", "来源文件名,1,200");
            mapTypeNew.put("RW_DB_VERSION", "版本号,1,50");
            mapTypeNew.put("RW_DB_STATUS", "状态,2,2");
            mapTypeNew.put("RW_DB_ADD_TIME", "添加时间,5,0");
            mapTypeNew.put("RW_DB_ADD_USER", "添加人,1,50");
            mapTypeNew.put("RW_DB_EDIT_TIME", "修改时间,5,0");
            mapTypeNew.put("RW_DB_EDIT_USER", "修改人,1,50");
            mapTypeNew.put("RW_DB_DELETE_REASON", "删除原因,1,200");
            mapTypeNew.put("RW_DB_ID", "系统编号,1,50");
            for (Map.Entry<String, String> entry : mapTypeNew.entrySet()) {
                TableDetail detail = new TableDetail();
                detail.setColumnName(entry.getKey());
                detail.setColumnChineseName(entry.getValue().split(",")[0]);
                detail.setColumnType(Integer.valueOf(entry.getValue().split(",")[1]));
                detail.setColumnLength(Integer.valueOf(entry.getValue().split(",")[2]));
                detail.setPrimaryKey(2);
                detail.setColumnNull(1);
                if (StringUtils.oneCase(entry.getKey(), "RW_DB_ID")) {
                    detail.setPrimaryKey(1);
                    detail.setColumnNull(2);
                }
                if (StringUtils.oneCase(entry.getKey(), "RW_DB_STATUS")) {
                    detail.setColumnDefault("1");
                }
                detailList.add(detail);
            }
            ConnectionHolder holder = (ConnectionHolder) TransactionSynchronizationManager.getResource(this.dataSource);
            Connection connection = holder.getConnection();
            if (null == connection) {
                throw new BusinessException("获取数据库连接出错，请稍后重试！");
            }
            table.setName(code);
            String sql = TableInfoUtil.creatTable(table, detailList, YssDataBaseEnum.MYSQL.getCode());
            if (StringUtils.isEmpty(sql)) {
                throw new BusinessException("生成的创建表SQL语句为空");
            }
            try {
                String sqlCreate = "DROP  TABLE  " + code;
                pStemt = connection.prepareStatement(sqlCreate);
                pStemt.execute();
            } catch (Exception ex) {
                log.error(ex.getMessage());
            }
            pStemt = connection.prepareStatement(sql);
            pStemt.execute();
            // 版本得备份信息，创建备份得表
            QueryWrapper<Description> descriptionQueryWrapper = new QueryWrapper<>();
            descriptionQueryWrapper.eq("NAME", ReportWorldDescriptionEnum.BACKUPVERSION.getCode());
            detailQueryWrapper.eq("STATUS", YssStatusEnum.NORMAL.getCode());
            Description description = descriptionMapper.selectOne(descriptionQueryWrapper);
            if (null == description || StringUtils.isBlank(description.getDescription())) {
                throw new BusinessException("没有获取到要备份的表，请稍后重试！");
            }
            Map<String, Object> mapTableName = JSON.parseObject(description.getDescription(), LinkedHashMap.class);
            if (CollectionUtils.isEmpty(mapTableName)) {
                throw new BusinessException("没有获取到备份表配置信息，请稍后重试！");
            }
            List<String> listTableName = (List<String>) mapTableName.get("tableNameList");
            if (CollectionUtils.isEmpty(listTableName)) {
                if (CollectionUtils.isEmpty(mapTableName)) {
                    throw new BusinessException("没有获取到备份表信息，请稍后重试！");
                }
            }
            List<String> backUpVersion = (List<String>) mapTableName.get("backUpVersion");
            Integer numberVersion = (Integer) mapTableName.get("numberVersion");
            if (CollectionUtils.isEmpty(backUpVersion)) { //没有备份表信息
                throw new BusinessException("没有获取到备份的信息，请稍后重试！");
            }
            for (String backVersion : backUpVersion) {
                String tableSuffix = StringUtils.getVersionSuffix(backVersion);
                if (StringUtils.isBlank(tableSuffix)) {
                    continue;
                }
                try {
                    String sqlCreate = "DROP  TABLE  " + code + tableSuffix;
                    pStemt = connection.prepareStatement(sqlCreate);
                    pStemt.execute();
                } catch (Exception ex) {
                    log.error(ex.getMessage());
                }
                String sqlCreate = "CREATE  TABLE  " + code + tableSuffix + " LIKE  " + code;
                pStemt = connection.prepareStatement(sqlCreate);
                pStemt.execute();
            }
            Map<String, Object> newMap = new HashMap<>();
            listTableName.add(code);
            newMap.put("backUpVersion", backUpVersion);
            newMap.put("numberVersion", numberVersion);
            newMap.put("tableNameList", listTableName);
            String json = JSONObject.toJSONString(newMap, SerializerFeature.WriteMapNullValue);
            description.setDescription(json);
            descriptionMapper.updateById(description);
            DatabaseDispose databaseDispose = new DatabaseDispose();
            databaseDispose.setTableName(code);
            databaseDispose.setHisVersion(database.getHisVersion());
            databaseDisposeService.initDispose(databaseDispose, true);
        } catch (Exception ex) {
            log.error(ex.getMessage());
            throw new BusinessException("构建基础信息失败" + ex.getMessage());
        } finally {
            try {
                JdbcUtil.close(pStemt);
            } catch (Exception ex) {
                log.error(ex.getMessage());
            }
        }
    }
}
